<?php

class DBUtils {
	
	public static function registerUser($inputs) {
	
		$pass = md5($inputs['password']);
		$connection = DBConnection::getDBConnection();
	
		$statement = "INSERT INTO profiles (email, user_name, password)
					  VALUES (:EMAIL,:USERNAME,:PASSWORD);
					  INSERT INTO users (level, exp, health, energy, money)
					  VALUES (1, 0, 25, 6, 10);";
	
		$stmt = $connection->prepare($statement);
		$stmt->bindValue(":USERNAME", $inputs['username'], PDO::PARAM_STR);
		$stmt->bindValue(":PASSWORD", $pass, PDO::PARAM_STR);
		$stmt->bindValue(":EMAIL", $inputs['email'], PDO::PARAM_STR);
		$stmt->execute();
	
		header("Location: index.php?action=registered");
	
	}
	
	public static function checkIfUserIsTaken($username) {
	
		$connection = DBConnection::getDBConnection();
	
		$statement = "SELECT user_name
					  FROM profiles
					  WHERE user_name=:USERNAME";
	
		$stmt = $connection->prepare($statement);
		$stmt->bindValue(":USERNAME", $username, PDO::PARAM_STR);
		$stmt->execute();
	
		$result = $stmt->fetch();
		return $result;
	}
	
	public static function checkIfEmailIsTaken($email) {
	
		$connection = DBConnection::getDBConnection();
	
		$statement = "SELECT user_name
					  FROM profiles
					  WHERE email=:EMAIL";
	
		$stmt = $connection->prepare($statement);
		$stmt->bindValue(":EMAIL", $email, PDO::PARAM_STR);
		$stmt->execute();
	
		$result = $stmt->fetch();
		return $result;
	}
	
	public static function getProfileData($username) {
		
		$connection = DBConnection::getDBConnection();
		
		$statement = "SELECT profile_id, user_name, email, password, first_name, last_name, status, profile_pic
					  FROM profiles
					  WHERE user_name=:USERNAME";
		
		$stmt = $connection->prepare($statement);
		$stmt->bindValue(":USERNAME", $username, PDO::PARAM_STR);
		$stmt->execute();
		
		$result = $stmt->fetch();
		return $result;		
	}
	
	public static function getUserData($userID) {
	
		$connection = DBConnection::getDBConnection();
	
		$statement = "SELECT u.user_id, 
							 u.level, 
							 u.exp, 
							 u.health, 
							 u.energy,
							 u.money, 
							 u.wins, 
							 u.looses, 
							 u.locked_period, 
							 u.locked_time, 
							 u.comp_battles, 
							 u.questions_reset, 
							 u.attack,
							 u.defence,
							 u.energy_restore,
							 p.user_name as user_name
					  FROM users u
					  INNER JOIN profiles p on u.user_id = p.profile_id
					  WHERE user_id=:USER_ID";
	
		$stmt = $connection->prepare($statement);
		$stmt->bindValue(":USER_ID", $userID, PDO::PARAM_STR);
		$stmt->execute();
	
		$result = $stmt->fetch();
		return $result;
	}
	
	public static function getMailData($userID) {
	
		$connection = DBConnection::getDBConnection();
	
		$statement = "SELECT p.profile_id as sender_name,
							 pr.profile_id as reciever_name,
							 m.msg,
							 m.not_send
                      FROM mail m
					  INNER JOIN profiles p on m.sender = p.profile_id
                      INNER JOIN profiles pr on m.reciever = pr.profile_id
					  WHERE pr.profile_id = :USER_ID";
		$stmt = $connection->prepare($statement);
		$stmt->bindValue(":MAIL_ID", $mailID, PDO::PARAM_STR);
		$stmt->bindValue(":USER_ID", $userID, PDO::PARAM_STR);
		$stmt->execute();
	
		$result = $stmt->fetch();
		return $result;
	}
	
	public static function updateUserData(array $userData) {
		
		$connection = DBConnection::getDBConnection();
		
		$statement = "UPDATE users
					  SET level=:LEVEL,
						  exp=:EXP,
						  health = :HEALTH,
						  energy = :ENERGY,
						  money = :MONEY, 
						  wins = :WINS, 
						  looses = :LOOSES,
						  locked_period = :LOCKED_PERIOD,
						  locked_time = :LOCKED_TIME,
						  comp_battles = :COMP_BATTLES,
						  questions_reset = :QUESTIONS_RESET,
						  attack = :ATTACK,
						  defence = :DEFENCE,
						  energy_restore = :ENERGY_RESTORE
						WHERE user_id=:USER_ID;";
		$stmt = $connection->prepare($statement);
		$stmt->bindValue(":USER_ID", $userData['user_id'], PDO::PARAM_INT);
		$stmt->bindValue(":LEVEL", $userData['level'], PDO::PARAM_INT);
		$stmt->bindValue(":EXP", $userData['exp'], PDO::PARAM_INT);
		$stmt->bindValue(":HEALTH", $userData['health'], PDO::PARAM_INT);		
		$stmt->bindValue(":ENERGY", $userData['energy'], PDO::PARAM_INT);
		$stmt->bindValue(":MONEY", $userData['money'], PDO::PARAM_INT);
		$stmt->bindValue(":WINS", $userData['wins'], PDO::PARAM_INT);
		$stmt->bindValue(":LOOSES", $userData['looses'], PDO::PARAM_INT);
		$stmt->bindValue(":LOCKED_PERIOD", $userData['locked_period'], PDO::PARAM_INT);
		$stmt->bindValue(":LOCKED_TIME",$userData['locked_time'], PDO::PARAM_STR);
		$stmt->bindValue(":COMP_BATTLES", $userData['comp_battles'], PDO::PARAM_INT);
		$stmt->bindValue(":QUESTIONS_RESET", $userData['questions_reset'], PDO::PARAM_BOOL);
		$stmt->bindValue(":ATTACK", $userData['attack'], PDO::PARAM_INT);
		$stmt->bindValue(":DEFENCE", $userData['defence'], PDO::PARAM_INT);
		$stmt->bindValue(":ENERGY_RESTORE", $userData['energy_restore'], PDO::PARAM_INT);
		
		$stmt->execute();
	}

	public static function insertMailData(array $mailData) {
	
		$connection = DBConnection::getDBConnection();
	
		$statement = "INSERT INTO `mail`(`SENDER`, `RECIEVER`, `MSG`, `NOT_SEND`)
					  VALUES (:SENDER,:RECIEVER, :MSG, :NOT_SEND)";
		$stmt = $connection->prepare($statement);
		$stmt->bindValue(":SENDER", $mailData['sender'], PDO::PARAM_INT);
		$stmt->bindValue(":RECIEVER", $mailData['reciever'], PDO::PARAM_INT);
		$stmt->bindValue(":MSG", $mailData['msg'], PDO::PARAM_INT);
		$stmt->bindValue(":NOT_SEND", $mailData['not_send'], PDO::PARAM_INT);	
		$stmt->execute();
	}
	
	public static function updateProfileData(array $profileData, $profile_id) {
		
		$connection = DBConnection::getDBConnection();
		
		$statement = "UPDATE profiles
                              SET first_name =:FIRST_NAME,
                                  last_name =:LAST_NAME,
                                  status =:STATUS
                              WHERE profile_id=:PROFILE_ID;";
		$stmt = $connection->prepare($statement);
		$stmt->bindValue(":PROFILE_ID", $profile_id, PDO::PARAM_INT);
		$stmt->bindValue(":FIRST_NAME", $profileData['first_name'], PDO::PARAM_STR);
		$stmt->bindValue(":LAST_NAME", $profileData['last_name'], PDO::PARAM_STR);
		$stmt->bindValue(":STATUS", $profileData['status'], PDO::PARAM_STR);		
		
		$stmt->execute();
	}
	
	public static function setPassword($password, $profileID) {
		
		$connection = DBConnection::getDBConnection();
		
		$statement = "UPDATE profiles
					  SET password = :PASSWORD
				      WHERE profile_id = :PROFILE_ID;";
		
		$stmt = $connection->prepare($statement);
		$stmt->bindValue(":PASSWORD", md5($password), PDO::PARAM_STR);
		$stmt->bindValue(":PROFILE_ID", $profileID, PDO::PARAM_INT);
		$stmt->execute();
		
	}
	
	public static function setEmail($email, $profileID) {
	
		$connection = DBConnection::getDBConnection();
	
		$statement = "UPDATE profiles
					  SET email = :EMAIL
				      WHERE profile_id = :PROFILE_ID;";
	
		$stmt = $connection->prepare($statement);
		$stmt->bindValue(":EMAIL", $email, PDO::PARAM_STR);
		$stmt->bindValue(":PROFILE_ID", $profileID, PDO::PARAM_INT);
		$stmt->execute();
	
	}
	
	public static function setProfilePic($profilePic, $profileID) {
		
		$connection = DBConnection::getDBConnection();
		
		$statement = "UPDATE profiles
					  SET profile_pic = :PROFILE_PIC
				      WHERE profile_id = :PROFILE_ID;";
		
		$stmt = $connection->prepare($statement);
		$stmt->bindValue(":PROFILE_PIC", $profilePic, PDO::PARAM_STR);
		$stmt->bindValue(":PROFILE_ID", $profileID, PDO::PARAM_INT);
		$stmt->execute();
	}
	
	public static function setStatus($status, $profileID) {
	
		$connection = DBConnection::getDBConnection();
	
		$statement = "UPDATE profiles
					  SET status = :STATUS
				      WHERE profile_id = :PROFILE_ID;";
	
		$stmt = $connection->prepare($statement);
		$stmt->bindValue(":STATUS", $status, PDO::PARAM_STR);
		$stmt->bindValue(":PROFILE_ID", $profileID, PDO::PARAM_INT);
		$stmt->execute();
	}
	
	public static function setFirstName($firstName, $profileID) {
	
		$connection = DBConnection::getDBConnection();
	
		$statement = "UPDATE profiles
					  SET first_name = :FIRST_NAME
				      WHERE profile_id = :PROFILE_ID;";
	
		$stmt = $connection->prepare($statement);
		$stmt->bindValue(":FIRST_NAME", $firstName, PDO::PARAM_STR);
		$stmt->bindValue(":PROFILE_ID", $profileID, PDO::PARAM_INT);
		$stmt->execute();
	}
	
	public static function setLastName($lastName, $profileID) {
	
		$connection = DBConnection::getDBConnection();
	
		$statement = "UPDATE profiles
					  SET last_name = :LAST_NAME
				      WHERE profile_id = :PROFILE_ID;";
	
		$stmt = $connection->prepare($statement);
		$stmt->bindValue(":LAST_NAME", $lastName, PDO::PARAM_STR);
		$stmt->bindValue(":PROFILE_ID", $profileID, PDO::PARAM_INT);
		$stmt->execute();
	}
	
	public static function getUnits() {
	
		$connection = DBConnection::getDBConnection();
	
		$statement = "SELECT unit_id, name, attack, deffence, price, description
					  FROM UNITS";
	
		$stmt = $connection->prepare($statement);
		$stmt->execute();
	
		$result = $stmt->fetchAll();
		return $result;
	}
	
	public static function getProfiles() {
	
		$connection = DBConnection::getDBConnection();
	
		$statement = "SELECT p.user_name, p.email, u.level, u.wins, u.looses
				      FROM profiles p
					  INNER JOIN users u on p.profile_id=u.user_id
					  ORDER BY u.level, u.wins;";

		$stmt = $connection->prepare($statement);
		$stmt->execute();
	
		$result = $stmt->fetchAll();
		return $result;
	}
	
	public static function getProfilesByLevel($level) {
	
		$connection = DBConnection::getDBConnection();
	
		$statement = "SELECT p.user_name, p.email, u.level, u.wins, u.looses
					 FROM profiles p
					 INNER JOIN users u on p.profile_id=u.user_id
					 WHERE u.level <= :LEVEL+2 and u.level >= :LEVEL-2
					 ORDER BY u.level, u.wins;";
	
		$stmt = $connection->prepare($statement);
		$stmt->bindValue(":LEVEL", $level, PDO::PARAM_STR);
		$stmt->execute();
	
		$result = $stmt->fetchAll();
		return $result;
	}
	
	public static function getQuestions() {
		
		$connection = DBConnection::getDBConnection();
		$statement = "SELECT `question`, `right_answer`, `wrong_answer_1`, `wrong_answer_2`, `wrong_answer_3`
				      FROM `questions`
					  ORDER BY RAND()
					  LIMIT 1";
		
		$stmt = $connection->prepare($statement);
		$stmt->execute();
		
		$result = $stmt->fetch();
		return $result;
		
	}
}

